package com.englisheveryday.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.englisheveryday.constants.Constants;
import com.englisheveryday.utils.L;
import com.englisheveryday.utils.StringUtil;
import com.englisheveryday.utils.UUIDGenerator;
import com.englisheveryday.vo.ArticleVO;
import com.englisheveryday.vo.PlaylistVO;

/**
 * <pre>
 *    
 * </pre>
 * @author 马永华
 * @date 2012-9-8 下午10:26:50
 * @version 1.1
 */

public class PlayListDAO  {

	private DBHelper  dbHelper;
	
	public PlayListDAO(Context context){
		dbHelper = new DBHelper(context, "/data/data/com.englisheveryday/englishdata.db");
	}
	
	//查询Playist
	public List<PlaylistVO> findAll(){
		List<PlaylistVO> playList = new  ArrayList<PlaylistVO>();
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try{
	    db = dbHelper.getWritableDatabase();
	    cursor = db.rawQuery(Constants.PLAYLIST_SELECT_SQL,null);
	    L.d(cursor.getCount()+"");
		while(cursor.moveToNext()){
			PlaylistVO vo = new PlaylistVO();
			vo.setTitle(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("title"))));
			vo.setAudio(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("audio"))));
			vo.setCount(cursor.getInt(cursor.getColumnIndex("count")));
			vo.setId(cursor.getString(cursor.getColumnIndex("id")));
			playList.add(vo);
		}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			if(db!=null&&db.isOpen())
				db.close();
			if(cursor!=null)
				cursor.close();
		}
		return playList;
	}
	
	/**
	 * 根据ID 查询
	 * @param id
	 * @return
	 */
	public PlaylistVO findById(String id){
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try{
	    db = dbHelper.getWritableDatabase();
	    cursor = db.rawQuery(Constants.PLAYLIST_SELECT_BY_ID,new String[]{id});
	    if(cursor.getCount()>0){
	    	cursor.moveToFirst();
	    	PlaylistVO vo = new PlaylistVO();
			vo.setTitle(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("title"))));
			vo.setAudio(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("audio"))));
			vo.setCount(cursor.getInt(cursor.getColumnIndex("count")));
			vo.setId(cursor.getString(cursor.getColumnIndex("id")));
			L.d("audio:"+vo.getAudio());
			return vo;
	    }
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			if(db!=null&&db.isOpen())
				db.close();
			if(cursor!=null)
				cursor.close();
		}
		return null;
	}
	
	/**
	 * 查询第一个
	 * @param id
	 * @return
	 */
	public PlaylistVO findFirst(){
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try{
	    db = dbHelper.getWritableDatabase();
	    cursor = db.rawQuery(Constants.PLAYLIST_SELECT_FIRST,null);
	    if(cursor.getCount()>0){
	    	cursor.moveToFirst();
	    	PlaylistVO vo = new PlaylistVO();
			vo.setTitle(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("title"))));
			vo.setAudio(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("audio"))));
			vo.setCount(cursor.getInt(cursor.getColumnIndex("count")));
			vo.setId(cursor.getString(cursor.getColumnIndex("id")));
			return vo;
	    }
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			if(db!=null&&db.isOpen())
				db.close();
			if(cursor!=null)
				cursor.close();
		}
		return null;
	}
	
	/**
	 * 查询最后一个
	 * @param id
	 * @return
	 */
	public PlaylistVO findLast(){
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try{
	    db = dbHelper.getWritableDatabase();
	    cursor = db.rawQuery(Constants.PLAYLIST_SELECT_LAST,null);
	    if(cursor.getCount()>0){
	    	cursor.moveToFirst();
	    	PlaylistVO vo = new PlaylistVO();
			vo.setTitle(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("title"))));
			vo.setAudio(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("audio"))));
			vo.setCount(cursor.getInt(cursor.getColumnIndex("count")));
			vo.setId(cursor.getString(cursor.getColumnIndex("id")));
			return vo;
	    }
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			if(db!=null&&db.isOpen())
				db.close();
			if(cursor!=null)
				cursor.close();
		}
		return null;
	}
	
	/**
	 * 根据ID 查询
	 * @param id
	 * @return
	 */
	public PlaylistVO findNextById(String id){
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try{
	    db = dbHelper.getWritableDatabase();
	    cursor = db.rawQuery(Constants.PLAYLIST_SELECT_NEXT,new String[]{id});
	    if(cursor.getCount()>0){
	    	cursor.moveToFirst();
	    	PlaylistVO vo = new PlaylistVO();
			vo.setTitle(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("title"))));
			vo.setAudio(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("audio"))));
			vo.setCount(cursor.getInt(cursor.getColumnIndex("count")));
			vo.setId(cursor.getString(cursor.getColumnIndex("id")));
			return vo;
	    }
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			if(db!=null&&db.isOpen())
				db.close();
			if(cursor!=null)
				cursor.close();
		}
		return null;
	}
	
	/**
	 * 根据ID 查询
	 * @param id
	 * @return
	 */
	public PlaylistVO findPreById(String id){
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try{
	    db = dbHelper.getWritableDatabase();
	    cursor = db.rawQuery(Constants.PLAYLIST_SELECT_PRE,new String[]{id});
	    if(cursor.getCount()>0){
	    	cursor.moveToFirst();
	    	PlaylistVO vo = new PlaylistVO();
			vo.setTitle(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("title"))));
			vo.setAudio(StringUtil.replaceNull(cursor.getString(cursor.getColumnIndex("audio"))));
			vo.setCount(cursor.getInt(cursor.getColumnIndex("count")));
			vo.setId(cursor.getString(cursor.getColumnIndex("id")));
			return vo;
	    }
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			if(db!=null&&db.isOpen())
				db.close();
			if(cursor!=null)
				cursor.close();
		}
		return null;
	}
	
	
    //添加到播放列表
	public void addtoPlayList(String id){
	  SQLiteDatabase db = null;
	  try{
		  db = dbHelper.getWritableDatabase();
		  if(findbyplaylistid(id,db)>0){
			L.d("已经存在了");
			return;
		 }
		 ContentValues cv = new ContentValues();
		 cv.put("articleid", id);
		 cv.put("id", UUIDGenerator.generate());
		 cv.put("count", 0);
		 db.insert("playlist", null, cv);
	  }catch(Exception e){
	   e.printStackTrace();
	  }finally {
		  if(db!=null&&db.isOpen())
				db.close();
	  }
	}
	
	//从播放列表删除
	public int deleteFromPlayList(String id){
		  SQLiteDatabase db = null;
		  int count = 0;
		  try{
			db = dbHelper.getWritableDatabase();
			db.delete("playlist", "id=?", new String[]{id});
		  }catch(Exception e){
		    e.printStackTrace();
		  }finally {
			  if(db!=null&&db.isOpen())
					db.close();
		  }
		  return count;
	}
	
	public int findAudioCount(){
		 SQLiteDatabase db = null;
		  try{
			db = dbHelper.getWritableDatabase();
			Cursor cursor = db.rawQuery(Constants.PLAYLIST_SELECT_COUNT,null);
			if(cursor.getCount()!=0){
				cursor.moveToFirst();
				return cursor.getInt(cursor.getColumnIndex("num"));
			}
			return 0;
		  }catch(Exception e){
		    e.printStackTrace();
		    return 0;
		  }finally {
			  if(db!=null&&db.isOpen())
					db.close();
		  }
	}
	
	//查询(PlayList)是否存在
	public int findbyplaylistid(String id,SQLiteDatabase db){
		Cursor cursor = db.rawQuery(Constants.PLAYLIST_COUNTSELECT, new String[]{id});
		int count = cursor.getCount();
		return count;
	}
}

